﻿IF OBJECT_ID('up_KML_InspVisitXmissionStructures') IS not NULL
	DROP PROC up_KML_InspVisitXmissionStructures 

GO 
 
CREATE PROCEDURE [dbo].[up_KML_InspVisitXmissionStructures]
	 @id int 
AS

DECLARE @TransmissionInspectionVisitsList varchar(MAX)
SET @TransmissionInspectionVisitsList = ''

SELECT 
	 @TransmissionInspectionVisitsList = @TransmissionInspectionVisitsList + CONVERT(varchar, [Vendor_InspVisit_XMissionStructure_PoleId]) + ','
FROM v_tb_Vendor_InspVisit_XmissionStructures_Poles Pole
WHERE
	Pole.Vendor_ProjectFK = @id


declare @settingValue varchar(500)
set @settingValue = (select SettingValue from tb_Settings where SettingId = 26)

declare @template varchar(max);
set @template = '
	<iframe 
		src="{url}{page}?ID=$[ID]" 
		style="width: 325px; height: 400px;"
		frameborder="0"  marginwidth="0" marginheight="0">Pole Position</iframe>
		$[description]'
select @template = replace(@template, '{url}', SettingValue)
from tb_Settings where SettingId = 23

declare @xml varchar(max);
select @xml = replace(convert(varchar(max),
(select 
	 'Pole Position Desktop' as 'Document/name'
	-- Is the mapicon filename correct?
	,(select
		'InspectionPoleXmission' as '@id'
		,.75 as 'IconStyle/scale'
		,replace(@template, '{page}', 'InspVisitXmissionStructure.aspx') as 'BalloonStyle/text'
		-- ,Replace(@settingValue, '{0}', 'mapicon_visit.png') as 'IconStyle/Icon/href'
		for xml path('Style'), type) as 'Document'
	
		,(select 
		
			-- what text should show up on the map?
			List.Value as 'name'

			-- all assets of this type get the same base style
			,'#InspectionPoleXmission' as 'styleUrl'

			-- should we show a special icon?
			,case Poles.ElectricalProblem
				when 1 then Replace(SettingValue, '{0}', 'mapicon_visit_electricproblem.png')
				else
				case Poles.InspectionResult
					when '1' then Replace(SettingValue, '{0}', 'mapicon_visit_okay.png')
					when '2' then Replace(SettingValue, '{0}', 'mapicon_visit_warning.png')
					when '3-1' then Replace(SettingValue, '{0}', 'mapicon_visit_redflag.png')
					when '3-2' then Replace(SettingValue, '{0}', 'mapicon_visit_redflag.png')
					when '3-3' then Replace(SettingValue, '{0}', 'mapicon_visit_redflag.png')
					when '3-4' then Replace(SettingValue, '{0}', 'mapicon_visit_redflag.png')
					else Replace(SettingValue, '{0}', 'mapicon_visit.png')
				end
			end as 'Style/IconStyle/Icon/href'

			-- should we show it in a special color?
			--,@TransmissionInspectionVisitsColor as 'Style/IconStyle/color'

			,'ID' as 'ExtendedData/Data/@name'
			
			,List.Value as 'ExtendedData/Data/value'

			-- where is it?
			,Convert(varchar, Longitude, 128) + ',' + Convert(varchar, Latitude, 128) + ',0' as 'Point/coordinates'
		from
			(select * from dbo.fn_Split(@TransmissionInspectionVisitsList, default)) as List
			join 
				(
				select 
					 vendor_inspvisit_xmissionstructureId as StructureId
					,Poles.vendor_inspvisit_xmissionstructure_poleId as PoleId
					,Poles.Utility_Asset_XmissionStructures_PoleFk as AssetId
					,Poles.Longitude as Longitude
					,Poles.Latitude as Latitude
					,Poles.Number as Number
					,(select count(*) from tb_vendor_inspvisit_xmissionstructures_images as images where
						images.vendor_inspvisit_xmissionstructure_poleFk = vendor_inspvisit_xmissionstructure_poleId) as ImageCount
					,(select Tops.Name
						from tb_XMissionStructures_TopTypes as Tops
						where Tops.XMissionStructures_TopTypeId = IsNull(TopTypeFk, XMissionStructures_TopTypeFk)) as TopType
					,(select Max(Types.Name)
						from tb_vendor_inspvisit_xmissionstructures_results as results 
						join tb_vendor_inspvisit_xmissionstructures_resultTypes types on
							results.Vendor_InspVisit_XmissionStructures_ResultTypeFk = types.Vendor_InspVisit_XmissionStructures_ResultTypeId
						where results.vendor_inspvisit_xmissionstructure_poleFk = vendor_inspvisit_xmissionstructure_poleId) as InspectionResult
					,(select Max(convert(int, isnull(results.bElectrical, 0)))
						from tb_vendor_inspvisit_xmissionstructures_results as results 
						where results.vendor_inspvisit_xmissionstructure_poleFk = vendor_inspvisit_xmissionstructure_poleId) as ElectricalProblem
				from 
					tb_vendor_inspvisit_xmissionstructures as structures
					RIGHT join tb_vendor_inspvisit_xmissionstructures_poles as poles
						on structures.vendor_inspvisit_xmissionstructureId = poles.vendor_inspvisit_xmissionstructureFk
				) as Poles
				on Poles.PoleId = Convert(int, List.Value)

			-- include setting for custom icon
			cross join tb_Settings
			where SettingId = 26

		for xml path('Placemark'), TYPE
		) as 'Document'	
		
for xml path('kml'))), '<kml>', '<?xml version="1.0" encoding="UTF-8"?><kml xmlns="http://earth.google.com/kml/2.2">')

select @xml;
